﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Liger.Data;
using Liger.Model;

namespace Test
{
    public class NorthwindSelectTest : BaseTest
    {
        public NorthwindSelectTest(DbContext db)
            : base(db)
        {
        }

         
        /// <summary>
        /// 测试 只检索指定几个字段
        /// </summary>
        public void TestListWithSelectFields()
        {
            var ListWithSelectFields = Db.From<Orders>()
                .Select(Orders._.OrderID,Orders._.OrderDate,Orders._.CustomerID)
                .Where(Orders._.Freight >= 40 && Orders._.Freight <= 50).ToList();

            this.AssertReturn(ListWithSelectFields.Count == 59);
        }



        /// <summary>
        /// 测试 count 字段
        /// </summary>
        public void TestCountField()
        {
            var Scalar = Db.From<Orders>()
                .Select(Orders._.Freight.Count())
                .Where(Orders._.Freight >= 40 && Orders._.Freight <= 50).ToScalar<int>();

            this.AssertReturn(Scalar == 59);
        }


        /// <summary>
        /// 测试 sum 字段
        /// </summary>
        public void TestSumField()
        {
            var Scalar = Db.From<Orders>()
                .Select(Orders._.Freight.Sum())
                .Where(Orders._.Freight >= 40 && Orders._.Freight <= 50).ToScalar<int>();

            this.AssertReturn(Scalar == 2666);
        }


        /// <summary>
        /// 测试 Min 字段
        /// </summary>
        public void TestMinField()
        {
            var Scalar = Db.From<Orders>()
                .Select(Orders._.Freight.Min())
                .Where(Orders._.Freight >= 40 && Orders._.Freight <= 50).ToScalar<decimal>();

            this.AssertReturn(Scalar == (decimal)40.26);
        }


        /// <summary>
        /// 测试 Max 字段
        /// </summary>
        public void TestMaxField()
        {
            var Scalar = Db.From<Orders>()
                .Select(Orders._.Freight.Max())
                .Where(Orders._.Freight >= 40 && Orders._.Freight <= 50).ToScalar<decimal>();

            this.AssertReturn(Scalar == (decimal)49.56);
        }

        /// <summary>
        /// 测试 两个字段组合
        /// </summary>
        public void TestTwoFieldCombine()
        {
            var Scalar = Db.From<Orders>()
                .Select((Orders._.Freight + Orders._.Freight).As("DoubleFreight"))
                .Where(Orders._.Freight >= 40 && Orders._.Freight <= 50)
                .OrderBy(Orders._.Freight.Asc)
                .ToScalar<decimal>();

            this.AssertReturn(Scalar == (decimal)80.52);
        }


        /// <summary>
        /// 测试 As
        /// </summary>
        public void TestAs()
        {
            var Scalar = Db.From<Orders>()
                .Select(Orders._.Freight.As("f"))
                .Where(Orders._.Freight >= 40 && Orders._.Freight <= 50)
                .OrderBy(Orders._.Freight.Asc)
                .ToScalar<decimal>();

            this.AssertReturn(Scalar == (decimal)40.26);
        }


        /// <summary>
        /// 测试 IsNull
        /// </summary>
        public void TestIsNull()
        {
            var Scalar = Db.From<Orders>()
                .Select(Orders._.Freight.IsNull(0))
                .Where(Orders._.Freight >= 40 && Orders._.Freight <= 50)
                .OrderBy(Orders._.Freight.Asc)
                .ToScalar<decimal>();

            this.AssertReturn(Scalar == (decimal)40.26);
        }


        /// <summary>
        /// 测试 Avg
        /// </summary>
        public void TestAvg()
        {
            var Scalar = Db.From<Orders>()
                .Select(Orders._.Freight.Avg())
                .Where(Orders._.Freight >= 40 && Orders._.Freight <= 50) 
                .ToScalar<int>();

            this.AssertReturn(Scalar == 45);
        }


        /// <summary>
        /// 测试 Len
        /// </summary>
        public void TestLen()
        {
            var Scalar = Db.From<Orders>()
                .Select(Orders._.CustomerID.Len().As("CustomerIDLength"))
                .Where(Orders._.Freight >= 40 && Orders._.Freight <= 50)
                .OrderBy(Orders._.Freight.Asc)
                .ToScalar<int>();

            this.AssertReturn(Scalar == 5);
        }


        /// <summary>
        /// 测试 Trim
        /// </summary>
        public void TestTrim()
        {
            var Scalar = Db.From<Orders>()
                .Select(Orders._.CustomerID.As("CustomerIDTrim"))
                .Where(Orders._.Freight >= 40 && Orders._.Freight <= 50)
                .OrderBy(Orders._.Freight.Asc)
                .ToScalar<string>();

            this.AssertReturn(Scalar == "WANDK");
        }


        /// <summary>
        /// 测试 Left
        /// </summary>
        public void TestLeft()
        {
            var Scalar = Db.From<Orders>()
                .Select(Orders._.CustomerID.Left(3))
                .Where(Orders._.Freight >= 40 && Orders._.Freight <= 50)
                .OrderBy(Orders._.Freight.Asc)
                .ToScalar<string>();

            this.AssertReturn(Scalar == "WAN");
        }

        /// <summary>
        /// 测试 Right
        /// </summary>
        public void TestRight()
        {
            var Scalar = Db.From<Orders>()
                .Select(Orders._.CustomerID.Right(2))
                .Where(Orders._.Freight >= 40 && Orders._.Freight <= 50)
                .OrderBy(Orders._.Freight.Asc)
                .ToScalar<string>();

            this.AssertReturn(Scalar == "DK");
        }


        /// <summary>
        /// 测试 Substring
        /// </summary>
        public void TestSubstring()
        {
            var Scalar = Db.From<Orders>()
                .Select(Orders._.CustomerID.Substring(2,2))
                .Where(Orders._.Freight >= 40 && Orders._.Freight <= 50)
                .OrderBy(Orders._.Freight.Asc)
                .ToScalar<string>();

            this.AssertReturn(Scalar == "AN");
        }


        /// <summary>
        /// 测试 Replace
        /// </summary>
        public void TestReplace()
        {
            var Scalar = Db.From<Orders>()
                .Select(Orders._.CustomerID.Replace("AND","RRR"))
                .Where(Orders._.Freight >= 40 && Orders._.Freight <= 50)
                .OrderBy(Orders._.Freight.Asc)
                .ToScalar<string>();

            this.AssertReturn(Scalar == "WRRRK");
        }


        /// <summary>
        /// 测试 Year
        /// </summary>
        public void TestYear()
        {
            var Scalar = Db.From<Orders>()
                .Select(Orders._.OrderDate.Year()) 
                .ToScalar<int>();

            this.AssertReturn(Scalar != 0);
        }


        /// <summary>
        /// 测试 Month
        /// </summary>
        public void TestMonth()
        {
            var Scalar = Db.From<Orders>()
                .Select(Orders._.OrderDate.Month()) 
                .ToScalar<int>();

            this.AssertReturn(Scalar != 0);
        }

        /// <summary>
        /// 测试 Day
        /// </summary>
        public void TestDay()
        {
            var Scalar = Db.From<Orders>()
                .Select(Orders._.OrderDate.Day()) 
                .ToScalar<int>();

            this.AssertReturn(Scalar != 0);
        }
    }
}
